﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace KnowledgeAssistant
{
    class StudentUtil
    {
     
        public static Student GetStudent(int student_id)
        {
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetStudent", new List<string>() { student_id.ToString() }).Tables[0];
            Group g = GroupUtil.GetGroup((int)table.Rows[0]["group_id"]);
            
            student = new Student(
                   int.Parse(table.Rows[0]["student_id"].ToString()),
                   table.Rows[0]["lname"].ToString(),
                   table.Rows[0]["mname"].ToString(),
                   table.Rows[0]["fname"].ToString(),
                   (int)table.Rows[0]["record_book_number"],
                   g
                   );
            return student;
        }

        public static List<Student> GetListStudents()
        {
            List<Student> students = new List<Student>();
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetStudent", new List<string>() { 0.ToString() }).Tables[0];
            
            foreach (DataRow r in table.Rows)
            {
                Group g = GroupUtil.GetGroup((int)r["group_id"]);
                student = new Student(
                       int.Parse(r["student_id"].ToString()),
                       r["lname"].ToString(),
                       r["mname"].ToString(),
                       r["fname"].ToString(),
                       (int)r["record_book_number"],
                       g
                       );
                students.Add(student);
            }
            return students;
        }

        public static List<Student> GetListStudentsForAddUser()
        {
            List<Student> students = new List<Student>();
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetStudentsListToAddUser").Tables[0];

            foreach (DataRow r in table.Rows)
            {
                Group g = GroupUtil.GetGroup((int)r["group_id"]);
                student = new Student(
                       int.Parse(r["student_id"].ToString()),
                       r["lname"].ToString(),
                       r["mname"].ToString(),
                       r["fname"].ToString(),
                       (int)r["record_book_number"],
                       g
                       );
                students.Add(student);
            }
            return students;
        }

        public static List<Student> GetListStudents(Group group)
        {
            List<Student> students = new List<Student>();
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetStudentsByGroup", new List<string>() { group.GroupID.ToString() }).Tables[0];

            foreach (DataRow r in table.Rows)
            {
                Group g = GroupUtil.GetGroup((int)r["group_id"]);
                student = new Student(
                       int.Parse(r["student_id"].ToString()),
                       r["lname"].ToString(),
                       r["mname"].ToString(),
                       r["fname"].ToString(),
                       (int)r["record_book_number"],
                       g
                       );
                students.Add(student);
            }
            return students;
        }

        public static List<Student> GetListStudentsWithUserData(Group group)
        {
            List<Student> students = new List<Student>();
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetStudentsByGroupWithUserData", new List<string>() { group.GroupID.ToString() }).Tables[0];

            foreach (DataRow r in table.Rows)
            {
                Group g = GroupUtil.GetGroup((int)r["group_id"]);
                student = new Student(
                       int.Parse(r["student_id"].ToString()),
                       r["lname"].ToString(),
                       r["mname"].ToString(),
                       r["fname"].ToString(),
                       (int)r["record_book_number"],
                       g
                       );
                student.Username = r["user_name"].ToString();
                student.Password = r["user_pass"].ToString();
                students.Add(student);
            }
            return students;
        }

        public static List<Student> GetListStudentForStatistics()
        {
            List<Student> students = new List<Student>();
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetAllStudents").Tables[0];

            foreach (DataRow r in table.Rows)
            {
                Group g = GroupUtil.GetGroup((int)r["group_id"]);
                student = new Student(
                       int.Parse(r["student_id"].ToString()),
                       r["lname"].ToString(),
                       r["mname"].ToString(),
                       r["fname"].ToString(),
                       (int)r["record_book_number"],
                       g
                       );
                student.UserID = (int)r["user_id"];
                students.Add(student);
            }
            return students;
        }

        public static void SaveStudent(Student student)
        {
            List<string> parameters = new List<string>();
            parameters.Add(student.LastName);
            parameters.Add(student.FirstName);
            parameters.Add(student.MiddleName);
            parameters.Add(student.RecordBookNumber.ToString());
            parameters.Add(student.Group.GroupID.ToString());
            if (student.StudentID == 0)
            {
                DatabaseConnect.execSPNonQuery("AddStudent", parameters);
            }
            else 
            {
                parameters.Add(student.StudentID.ToString());
                DatabaseConnect.execSPNonQuery("UpdateStudent", parameters);
            }
        }

        public static void DeleteStudent(Student student)
        {
            List<string> parameters = new List<string>();
            parameters.Add(student.StudentID.ToString());
            DatabaseConnect.execSPNonQuery("DeleteStudent", parameters);
        }

        public static List<Student> GetListStudentsForCheckExist(Group group)
        {
            List<Student> students = new List<Student>();
            Student student = null;
            DataTable table = DatabaseConnect.execSP("GetStudentsByGroup", new List<string>() { group.GroupID.ToString() }).Tables[0];

            foreach (DataRow r in table.Rows)
            {
                Group g = GroupUtil.GetGroup((int)r["group_id"]);
                student = new Student(
                       0,
                       r["lname"].ToString(),
                       r["mname"].ToString(),
                       r["fname"].ToString(),
                       (int)r["record_book_number"],
                       g
                       );
                students.Add(student);
            }
            return students;
        }
    }
}
